Exploring Data Integration in Power Platform

For some months ago I started to explore the data integration feature in Power Platform. It was one of my brilliant colleagues, Marica Söderstam, who came up with an idea that got me to deep dive into this feature. Thanks Marica!

Data Integration is an integration service which has been available since July 2017 and it can be used for integrating data from multiple sources into the Common Data Service for Apps (CDS).

Update 2020-07-08: As part of the 2019 Release Wave 2 Data Integration in Maker Portal was rebranded to Dataflows. This article mentions Data Integration in Power Apps Web Portal, that refers to what we today know as Power Platform Dataflows in Maker Portal.

The below picture is from the data integration part of the April release notes and it illustrates e.g. that there are a lot of pre-defined connectors which can be used when setting up your data integration projects. You also have the possibility to create custom connectors if you can not find one that suits your needs.

Data Integration comes with a set of pre-defined templates, source to destination, field by field, which can be used e.g. for integrating products within the Dynamics 365 family. But you can also create your own templates and integrate data from a source of your own choice.

At least in theory – if you read on further I will let you know my findings and a small work-around for integrating from other sources than what exists in the pre-defined templates.

In our case we use the data integration feature to write data from an onprem source to the CDS and a Dynamics 365 App. You can read about Model-Driven Apps here and about creating apps for Dynamics 365 Customer Engagement here.

How to get started

So now we have a new integration tool within the Power Platform, seams like it can be pretty useful! Now what, well, first you need to know where to start.

Let us assume you already have your environments set up. You can access the data integration feature from the Power Apps Admin Center or from the new Power Platform Admin Center. There you will have access to the templates and in there is probably where you should be setting up your data integration projects, if only you get all the pieces together.

In order to get started you need to create a connection (provide credentials to the data source). Then you use that connection in what is called a connection set, where you also specify at least one more connection. After that you are ready to create your first data integration project. You can read about the details here.

An alternative way

In our case we ran into some issues when setting up the connection set though. As already mentioned our destination is a Dynamics 365 App, a Model-Driven App build on Dynamics 365 for CE. Our source is an onprem source (SQL Server) so we installed a data gateway and set up a connection to it. But for some reason we were not able to access it from within the admin center.

Very frustrating, however we found another way of setting up the data integration projects. By using the Power Apps Web Portal we managed to set up what we had planned and that is how the web portal became our choice of user interface for this service.

In the Power Apps Web Portal you find “Data integration” under the “Data” menu item.

I have not seen any recommendations from Microsoft regarding which user interface to use, web portal or admin center. However I have been told from them that you should not mix those two, even though technically you are able to create a project in the web portal and then switch to the admin center to view it or schedule it. Right-clicking on a project in the web portal and choosing “Show refresh history” (i.e. execution history) even takes you to the other user interface in the admin center. The future will tell how all this will cooperate later on.

Conclucions and to be continued

I would recommend to start in the admin center, if you are able to connect to your data sources from in there and create connection sets, that is probably where you want to be, considering the fact that you can work with templates in there. You also get a better overview of scheduled and run jobs and in the documentation from Microsoft the service is called “Data integrator (for Admins)”, which could indicate that you are supposed to be in the admin center.

This is the first blog post of three regarding findings from exploring Data Integration in Power Platform. In the next blog post I will describe how to set up a data integration project from within the web portal and in the third and last blog post I will share tips and tricks which I have learned along the way exporing this new feature.

By the way, the tips and tricks will be of interest for those who use either or of the user interfaces.

Sources and useful links

Integrate data into Common Data Service

Release Notes October 2018 – Data Integration

Release Notes April 2019 – Data Integration

Install and configure an On-Premises Data Gateway